﻿use persons
go
------------------11---------------------------------------------------------------
/*Find grandfather's contacts*/
SELECT 
[Contact].[ContactType], 
[Contact].[ContactValue],
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Contact] INNER JOIN [dbo].[Person] ON [Contact].[fk_personId]=[Person].[ID] 
WHERE [Person].[ID] IN( SELECT  
[Person].[ID] 
FROM [dbo].[Person]
WHERE [Person].[ID] IN(SELECT DISTINCT  
[Person].[fk_fatherId]
FROM [dbo].[Person] 
WHERE  [Person].[ID] IN (SELECT DISTINCT 
[Person].[fk_motherId]
FROM [dbo].[Person] 
WHERE [Person].[fk_motherId] IS NOT NULL
UNION
SELECT DISTINCT 
[Person].[fk_fatherId]
FROM [dbo].[Person] 
WHERE [Person].[fk_fatherId] IS NOT NULL)))

SELECT 
[Contact].[ContactType], 
[Contact].[ContactValue], 
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Contact], [dbo].[Person]
WHERE [Contact].[fk_personId]=[Person].[ID] AND [Person].[ID] IN(SELECT 
[Person].[ID] 
FROM [dbo].[Person]
WHERE [Person].[ID] IN(SELECT DISTINCT  
[Person].[fk_fatherId]
FROM [dbo].[Person] 
WHERE  [Person].[ID] IN (SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person], [dbo].[Person] AS [MOTHER] 
WHERE [Person].[ID]=[MOTHER].[fk_motherId] AND [Person].[fk_motherId] IS NOT NULL
UNION
SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person], [dbo].[Person] AS [FATHER] 
WHERE [Person].[ID]=[FATHER].[fk_fatherId] AND [Person].[fk_fatherId] IS NOT NULL)))

SELECT 
[Contact].[ContactType], 
[Contact].[ContactValue], 
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Contact] INNER JOIN [dbo].[Person]
ON [Contact].[fk_personId]=[Person].[ID] 
WHERE [Person].[ID] IN(SELECT [Person].[ID] 
FROM [dbo].[Person]
WHERE [Person].[ID] IN(SELECT DISTINCT  
[Person].[fk_fatherId]
FROM [dbo].[Person] 
WHERE  [Person].[ID] IN (SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [MOTHER] ON [Person].[ID]=[MOTHER].[fk_motherId]
WHERE [Person].[fk_motherId] IS NOT NULL
UNION
SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [FATHER] ON [Person].[ID]=[FATHER].[fk_fatherId] 
WHERE [Person].[fk_fatherId] IS NOT NULL)))